PostgreSQL Configuration File: Less is More

PGconf.de
2025-05-08

Who am I

• Lætitia Avrot
• PostgreSQL recognized contributor
• Former PostgreSQL Europe board member
• #PostgresWomen co-founder
• EDB Practice Leader
• University teacher in Lyon
• mydbanotebook.org / psql-tips.org

Configuration File: Less is More

• The philosophy
• The 3 key practices
• Implementation and results

The Problem

• Cluttered configs
• Redundant settings
• Unnecessary comments
• Ticking time bomb

Real Consequences

• Production outages
• Performance issues
• Wasted time
• Troubleshooting hell
• Frustration
• Demotivation
• Turnover

Less is More

• Minimal config
• Only essentials
• Maximum clarity

Version Control

• Git essential
• Track changes
• Document reasons

Good Git Practice

$ git commit -m "Increase shared_buffers to 8GB
  
Reason: Memory upgrade from 16GB to 32GB
Tested: staging environment 
Ticket: DBA-1234"

1. Clean

• Remove comments
• Remove defaults
• Keep only changes

Before vs After

# - Memory -

#shared_buffers = 128MB
#huge_pages = try
#max_prepared_transactions = 0
shared_buffers = 4GB

shared_buffers = 4GB

2. Simplify

• No duplicates
• Avoid ALTER SYSTEM
• One screen rule

The ALTER SYSTEM Problem

# postgresql.conf
work_mem = 16MB

# postgresql.auto.conf (via ALTER SYSTEM)
work_mem = 32MB

# Which one wins?

3. Organize

3. Organize

3. Organize

• Use includes
• Theme-based files
• Logical structure

Include Structure

include 'conf.d/memory.conf'
include 'conf.d/connections.conf'
include 'conf.d/replication.conf'
include 'conf.d/logging.conf'
include 'conf.d/vacuum.conf'

Include Structure

include 'conf.d'

Production-Ready Config

# PostgreSQL 15 Production Config
# Server: db-prod-01
# Last updated: 2025-04-01
listen_addresses = '*'
ssl = on

shared_buffers = 8GB
work_mem = 128MB
maintenance_work_mem = 2GB
autovacuum_work_mem = 1GB
max_connections = 500
temp_file_limit = 1GB

Production-Ready Config

archive_mode = on
archive_command = 'pgbackrest --stanza=prod archive-push %p'

random_page_cost = 1.5
effective_cache_size = 24GB

Production-Ready Config

log_destination = 'csvlog'
logging_collector = on
log_directory = '/var/log/postgresql'
log_rotation_size = 1GB
log_min_duration_statement = 250ms
log_autovacuum_min_duration = 0
log_checkpoints = on
log_connections = on
log_disconnections = on
log_lock_waits = on
log_temp_files = 0

Production-Ready Config

statement_timeout = 10min
idle_in_transaction_session_timeout = 30min

deadlock_timeout = 100ms

Production-Ready Config

# PostgreSQL 15 Production Config
# Server: db-prod-01
# Last updated: 2025-04-01

include 'conf.d/memory.conf'
include 'conf.d/connections.conf'
include 'conf.d/replication.conf'
include 'conf.d/logging.conf'

Applying to pg_hba.conf

• Security configuration is critical
• Often neglected in maintenance
• Same principles apply
• Even higher security impact

pg_hba.conf: Before

# PostgreSQL Client Authentication Configuration File
# ===================================================
#
# TYPE  DATABASE        USER            ADDRESS                 METHOD
# IPv4 local connections:
host    all             all             127.0.0.1/32            md5
# IPv6 local connections:
host    all             all             ::1/128                 md5
# Allow replication connections from localhost
host    replication     all             192.168.1.10/32         md5
host    replication     all             ::1/128                 md5
host    marketing_db    john            10.0.2.50/32         scram-sha-256
host    marketing_db    maria           10.0.2.50/32         scram-sha-256
host    sales_db        sales_user      192.168.1.10/32         md5
host    sales_db        sales_user      192.168.1.11/32         md5
host    sales_db        sales_user      192.168.1.12/32         md5
host    marketing_db    robert          10.0.2.50/32         scram-sha-256
host    marketing_db    sarah           10.0.2.50/32         scram-sha-256
host    crm_db          crm_user        192.168.1.20/32         md5
host    crm_db          crm_user        192.168.1.21/32         md5
host    marketing_db    david           10.0.2.50/32         scram-sha-256

pg_hba.conf: After

# LOCAL ACCESS
local   all             postgres                                peer
host    all             all             t127.0.0.1/32           scram-sha-256
host    all             all             ::1/128                 scram-sha-256

# APPLICATION SERVERS
host    sales_db        +sales_user     192.168.1.0/24          scram-sha-256
host    crm_db          +crm_user       192.168.1.0/24          scram-sha-256
host    marketing_db    +marketing_user 10.0.2.50/32            scram-sha-256

# REPLICATION
host    replication     all             10.0.0.0/24             scram-sha-256

Benefits

• Faster troubleshooting
• Reduced risk
• Better performance
• Team clarity

Remember: Three Rules

• Clean ruthlessly
• Simplify always
• Organize logically

Take Action

• Audit today
• Start Git
• Apply three rules

Questions?

Thank You!